<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Queries</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, Perl, DBI, queries, fetchting data, 
tutorial, database, programming, development">
<meta name="description" content="In this part of the SQLite Perl tutorial, 
we demonstrate how to fetch data from the database using various Perl DBI methods.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>


<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Queries</h1>

<p>
We have already established a connection to the database. Now we 
are going modify and fetch the data from the database. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Data is retrieved from the database with the SELECT statement. In Perl DBI, first
we prepare the SQL statement with the <code>prepare()</code> method. The SQL string
is sent to the database engine, which checks the statement validity, syntax and in
some databases also the user permissions to perform certain queries. If all is OK, a
reference to the statement handle is returned to the Perl script. The next step
is the call to the <code>execute()</code> method. The method executes the query
within the database. At this moment the result stays in the database. The Perl script
does not contain the data yet. For non-select statements, the <code>execute()</code> 
method returns the number of rows affected, if known. In the last step the data is fetched from
the database. The data is pulled row by row and populated into the Perl data structures. 
</p>

<p>
The Perl DBI has several methods to fetch data from database tables. 
</p>

<table>
    <tr class="hdr"><th>Method</th><th>Description</th></tr>
    <tr><td>fetchrow_arrayref()</td><td>Fetches the next row of data and returns a reference to an array.</td></tr>
    <tr class="gray"><td>fetchrow_array()</td><td>Fetches the next row of data and returns it as a list.</td></tr>
    <tr><td>fetchrow_hashref()</td><td>Fetches the next row of data and returns it as a reference to a hash.</td></tr>
    <tr class="gray"><td>fetchall_arrayref()</td><td>Fetches all data &amp; returns a reference to an array that has one reference per row.</td></tr>
    <tr><td>fetch()</td><td>The method is an alias for fetchrow_arrayref().</td></tr>
    <tr class="gray"><td>fetchrow()</td><td>The method is an alias for fetchrow_array().</td></tr>
</table>

<p>
After the SQL statement was prepared and executed, we call one of the available fetch methods. 
</p>

<table>
    <tr class="hdr"><th>Method</th><th>Description</th></tr>    
    <tr><td>selectrow_arrayref()</td><td>Combines prepare(), execute() and fetchrow_arrayref() into a single call</td></tr>
    <tr class="gray"><td>selectrow_hashref()</td><td>Combines prepare(), execute() and fetchrow_hashref() into a single call</td></tr>
    <tr><td>select_row_array()</td><td>Combines prepare(), execute() and fetchrow_array() into a single call.</td></tr>
    <tr class="gray"><td>selectall_arrayref()</td><td>Combines prepare(), execute() and fetchall_arrayref() into a single call.</td></tr>
    <tr><td>selectall_hashref()</td><td>Combines prepare(), execute() and fetchall_hashref() into a single call.</td></tr>
    <tr class="gray"><td>selectcol_arrayref()</td><td>Combines prepare(), execute() and fetching one col from all rows into a single call.</td></tr>
</table>

<p>
In the second table we have a list of utility methods which combine three methods
into one call. They are convenience methods.
</p>

<h2>The fetch methods</h2>


<p>
In the first example we will demonstrate the usage of the 
<code>fetchrow_arrayref()</code> method. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");
$sth->execute();

my $row;
while ($row = $sth->fetchrow_arrayref()) {
    print "@$row[0] @$row[1] @$row[2]\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the example we select 5 rows from the Cars table. The
data is retrieved with the <code>fetchrow_arrayref()</code>
method.
</p>

<pre class="explanation">
my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");
$sth->execute();
</pre>

<p>
These are the first two phases of the data retrieval process. 
We prepare and execute the SELECT statement. 
</p>

<pre class="explanation">
my $row;
while ($row = $sth->fetchrow_arrayref()) {
    print "@$row[0] @$row[1] @$row[2]\n";
}
</pre>

<p>
Now we are fetching the data. The <code>fetchrow_arrayref()</code> method 
fetches the next row of data and returns a reference to an array holding 
the field values. We put the method in the while loop which terminates,
when there are no more rows left. 
</p>

<pre>
$ ./fetchrow_arrayref.pl
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
Example output.
</p>

<hr class="btm">

<p>
In the second example, we will use the <code>fetchrow_array()</code> method.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" );  
$sth->execute();
      
my @row;
while (@row = $sth->fetchrow_array()) {
    print "@row\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In this script we connect to the database and fetch 5 rows
of the Cars table one by one using the <code>fetchrow_array()</code> method. 
</p>

<pre class="explanation">
my @row;
while (@row = $sth->fetchrow_array()) {
    print "@row\n";
}
</pre>

<p>
The <code>fetchrow_array()</code> method fetches the next row of data 
and returns it as a list containing the field values. We use the while
loop to go through all 5 rows. 
</p>

<hr class="btm">

<p>
In the next example, we will fetch data by their column names.
For this we will utilize the <code>fetchrow_hashref()</code> method. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" );  
$sth->execute();
      
my $row;
while($row = $sth->fetchrow_hashref()) {
    print "$row->{Id} $row->{Name} $row->{Price}\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the example, the data is returned in the form of a reference to
a Perl hash.
</p>

<pre class="explanation">
my $row;
while($row = $sth->fetchrow_hashref()) {
    print "$row->{Id} $row->{Name} $row->{Price}\n";
}
</pre>

<p>
The <code>fetchrow_hashref()</code> method fetches the next row of data 
and returns it as a reference to a hash containing field name and field 
value pairs. With this method, we can retrieve the values by their column
names. 
</p>

<hr class="btm">

<p>
In the last example of this section, we fetch all data from the SELECT
statement at one step. We use the <code>fetchall_arrayref()</code> method.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;


my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5");
$sth->execute();

my $all = $sth->fetchall_arrayref();

foreach my $row (@$all) {
    my ($id, $name, $price) = @$row;
    print "$id $name $price\n";
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
The example selects and prints five rows from the Cars table.
</p>

<pre class="explanation">
my $all = $sth->fetchall_arrayref();
</pre>

<p>
We fetch all data in one method call. The <code>fetchall_arrayref()</code>
method returns a reference to an array that contains one reference 
per row. 
</p>

<pre class="explanation">
foreach my $row (@$all) {
    my ($id, $name, $price) = @$row;
    print "$id $name $price\n";
}
</pre>

<p>
We use the foreach loop to go through the retrieved data. 
</p>


<h2>Dumping data</h2>

<p>
Perl DBI has a special method called <code>dump_results()</code>.
This method is designed as a handy utility for prototyping and testing
queries. It uses a <code>neat_list()</code> method to format and edit 
the string for reading by humans. It is not recommended for data transfer 
applications.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db",     
    "",
    "",          
    { RaiseError => 1 } 
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" );  
$sth->execute();

$sth->dump_results();      

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the example we will dump all the data from the result set. 
</p>

<pre class="explanation">
my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" );  
$sth->execute();
</pre>

<p>
The SQL statement selects five rows from the Cars table. 
And all three columns.
</p>

<pre class="explanation">
$sth->dump_results();  
</pre>

<p>
The <code>dump_results()</code> selects all rows from the 
statement handle and prints them. It is a method used for prototyping
and testing. 
</p>

<pre>
$ ./dump.pl 
1, 'Audi', 52642
2, 'Mercedes', 57127
3, 'Skoda', 9000
4, 'Volvo', 29000
5, 'Bentley', 350000
5 rows
</pre>

<p>
This is the output of the example.
</p>


<h2>The convenience methods</h2>

<p>
We will show two examples that will use the aforementioned convenience
methods. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $ary = $dbh->selectrow_arrayref("SELECT * FROM Cars WHERE Id = 5");
print join(" ", @$ary), "\n";

$dbh->disconnect();
</pre>

<p>
In the first code example, we will call the <code>selectrow_arrayref()</code>
method. We select the fifth row from the Cars table. 
</p>

<pre class="explanation">
my $ary = $dbh->selectrow_arrayref("SELECT * FROM Cars WHERE Id = 5");
</pre>

<p>
The <code>selectrow_arrayref()</code> method combines the <code>prepare()</code>, 
<code>execute()</code> and <code>fetchrow_arrayref()</code> into a single call. 
It returns a reference to the first row of data from the statement. Note that 
we do not use the statement handle. We use the $dbh database handle object. 
</p>

<pre class="explanation">
print join(" ", @$ary), "\n";
</pre>

<p>
We print the row to the console. 
</p>

<hr class="btm">

<p>
The following example shows the <code>selectall_arrayref()</code>
method.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $all = $dbh->selectall_arrayref("SELECT * FROM Cars LIMIT 5");

foreach my $row (@$all) {
    my ($id, $name, $price) = @$row;
    print "$id $name $price\n";
}

$dbh->disconnect();
</pre>

<p>
We again retrieve 5 rows from the Cars table.
</p>

<pre class="explanation">
my $all = $dbh->selectall_arrayref("SELECT * FROM Cars LIMIT 5");
</pre>

<p>
The <code>selectall_arrayref()</code> method returns a reference to an array 
containing a reference to an array for each row of data fetched.
The supplied SQL statement selects 5 rows from the Cars table. Note
that we did not call neither the <code>prepare()</code>, nor the <code>execute()</code>
method. It is because the  <code>selectall_arrayref()</code> method combines 
<code>prepare()</code>, <code>execute()</code> and <code>fetchall_arrayref()</code> 
into one single call.
</p>

<pre class="explanation">
foreach my $row (@$all) {
    my ($id, $name, $price) = @$row;
    print "$id $name $price\n";
}
</pre>

<p>
We go through the fetched array of arrays and print the data to the 
terminal. 
</p>

<pre>
$ ./retrieve.pl
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
This is the output of the example.
</p>


<h2>Parameterized queries</h2>

<p>
Now we will concern ourselves with parameterized queries. When we 
use parameterized queries, we use placeholders instead of directly 
writing the values into the statements. Parameterized queries increase 
security and performance.
</p>

<p>
A programmer must be always cautious when the program receives an input
from the user. Instead of building a string from the user input, we 
bind the value later to the prepared statement. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                         
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $id = 3;
my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id = ?" );  
$sth->execute($id);

my $ret = $sth->fetch();

foreach my $row (@$ret) {
    print "$row ";
} 

print "\n";

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the code example, we select a specific row from the table. 
The SQL statement has one placeholder, which is filled later in the code.
</p>

<pre class="explanation">
my $id = 3;
</pre>

<p>
This could be an input from the user. 
</p>

<pre class="explanation">
my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id = ?" ); 
</pre>

<p>
The question mark (?) is a placeholder for a value. The value is added
later.
</p>

<pre class="explanation">
$sth->execute($id);
</pre>

<p>
The <code>execute()</code> statement takes one parameter, which is bound
to the placeholder.  
</p>

<pre>
$ ./parameterized.pl
3 Skoda 9000 
</pre>

<p>
We have retrieved one row from the Cars table using a parameterized query.
</p>

<hr class="btm">

<p>
In the second example, we will use a parameterized query using one
of the convenience select methods. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                         
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $id = 2;

my @ary = $dbh->selectrow_array("SELECT * FROM Cars WHERE Id = ?", undef, $id);
print join(" ", @ary), "\n";

$dbh->disconnect();
</pre>

<p>
We have one placeholder to be filled in the SELECT query. 
</p>

<pre class="explanation">
my @ary = $dbh->selectrow_array("SELECT * FROM Cars WHERE Id = ?", undef, $id);
</pre>

<p>
The third parameter of the <code>selectrow_array()</code> method takes a 
value for the placeholder. 
</p>

<p>
In this part of the SQLite Perl tutorial, we have demonstrated how to fetch data
from the database using various Perl DBI methods. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 6, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

